package com.hsnn.medstgmini.settlement.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.settlement.dao.PaymentDao;
import com.hsnn.medstgmini.settlement.model.PaymentStatus;
import com.hsnn.medstgmini.settlement.model.SetAcctTransInfo;
import com.hsnn.medstgmini.settlement.model.SetPayment;
import com.hsnn.medstgmini.settlement.model.SetPaymentDetail;
import com.hsnn.medstgmini.settlement.model.SetPaymentPayDetail;
import com.hsnn.medstgmini.settlement.model.SetSettlement;
import com.hsnn.medstgmini.settlement.model.SetSettlementDetail;
import com.hsnn.medstgmini.settlement.model.form.Payment;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.CodeUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;

/**
 * @category 支付单
 * @author 蔡春龙
 * @date 2015年7月27日
 */
@Repository
public class PaymentDaoImpl implements PaymentDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 查询支付单列表
	 * @date 2015年7月27日
	 * @param payment
	 * @param page
	 * @return
	 */
	public Pagination getPaymentList(Payment payment, Pagination page) {
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.payment_bill_id paymentBillId, a.payment_bill_code paymentBillCode, a.payment_bill_name paymentBillName, a.total_orders totalOrders, a.total_amount totalAmount, "
				+ "DATE_FORMAT(a.create_datetime, '%Y-%m-%d %T') createDateTime, a.create_user createUser, b.name createUserName, a.remark, "
				+ "a.submit_user submitUser, c.name submitUserName, DATE_FORMAT(a.submit_datetime, '%Y-%m-%d %T') submitDateTime, a.status, "
				+ "DATE_FORMAT(a.audit_datetime, '%Y-%m-%d %T') auditDateTime, a.audit_user auditUser, d.name auditUserName, a.audit_remark auditRemark, "
				+ "DATE_FORMAT(a.reaudit_datetime, '%Y-%m-%d %T') reauditDateTime, a.reaudit_user reauditUser, e.name reauditUserName, "
				+ "a.reaudit_remark reauditRemark, a.bill_settle_datetime billSettleDatetime, a.bill_settle_user billSettleUser,"
				+ " a.upd_datetime updDateTime, a.upd_user updUser "
				+ "FROM set_payment a "
				+ "LEFT JOIN sys_user b on a.create_user = b.id "
				+ "LEFT JOIN sys_user c on a.submit_user = c.id "
				+ "LEFT JOIN sys_user d on a.audit_user = d.id "
				+ "LEFT JOIN sys_user e on a.reaudit_user = e.id WHERE 1 = 1 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if (payment != null) {
			Integer status = payment.getStatus();
			if (status != null) {
				listSb.append(" AND a.status = :status ");
				params.put("status", status);
			}
			String paymentBillCode = payment.getPaymentBillCode();
			if (StringUtils.isNotBlank(paymentBillCode)) {
				listSb.append(" AND a.payment_bill_code = :paymentBillCode ");
				params.put("paymentBillCode", paymentBillCode);
			}
			String paymentBillName = payment.getPaymentBillName();
			if (StringUtils.isNotBlank(paymentBillName)) {
				listSb.append(" AND a.payment_bill_name LIKE :paymentBillName ");
				params.put("paymentBillName", "%"+paymentBillName.trim()+"%");
			}
			String startDate = payment.getCreateTimeBegin();
			if (StringUtils.isNotBlank(startDate)) {
				listSb.append(" AND date_format(a.create_datetime,'%Y-%m-%d') >= :startDate ");
				params.put("startDate", startDate);
			}
			String endDate = payment.getCrateTimeEnd();
			if (StringUtils.isNotBlank(endDate)) {
				listSb.append(" AND date_format(a.create_datetime,'%Y-%m-%d') <= :endDate ");
				params.put("endDate", endDate);
			}
		}

		listSb.append(" order by a.create_datetime desc ");
		return dao.getSqlPageMapList(listSb.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 获取支付单查看列表数据
	 *@author 邱磊
	 *@date 2015年8月9日 下午3:25:15
	 *@param payment
	 *@param page
	 *@return
	 */
	public Pagination getSubmitPaymentList(Pagination page){
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.payment_bill_id paymentBillId, a.payment_bill_code paymentBillCode, a.payment_bill_name paymentBillName,")
		.append("  a.total_orders totalOrders, a.total_amount totalAmount, DATE_FORMAT(a.create_datetime, '%Y-%m-%d %T') createDateTime,")
		.append("  a.create_user createUser, b.name createUserName, a.remark, a.submit_user submitUser, c.name submitUserName,")
		.append("  DATE_FORMAT(a.submit_datetime, '%Y-%m-%d %T') submitDateTime, a.status, DATE_FORMAT(a.audit_datetime, '%Y-%m-%d %T') auditDateTime,")
		.append("  a.audit_user auditUser, d.name auditUserName, a.audit_remark auditRemark, DATE_FORMAT(a.reaudit_datetime, '%Y-%m-%d %T') reauditDateTime,")
		.append("  a.reaudit_user reauditUser, e.name reauditUserName, a.reaudit_remark reauditRemark, DATE_FORMAT(a.bill_settle_datetime, '%Y-%m-%d %T') billSettleDatetime,")
		.append("  f.name billSettleUser, a.upd_datetime updDateTime, a.upd_user updUser ")
		.append(" FROM set_payment a LEFT JOIN sys_user b on a.create_user = b.id LEFT JOIN sys_user c on a.submit_user = c.id ")
		.append(" LEFT JOIN sys_user d on a.audit_user = d.id LEFT JOIN sys_user e on a.reaudit_user = e.id left join sys_user f on f.id=a.bill_settle_user WHERE a.status != 0 ");
		Map<String, Object> params = new HashMap<String, Object>();
		Payment payment = (Payment) page.getForm();
		if (payment != null) {
			if (payment.getStatus() != null) {
				listSb.append(" AND a.status = :status ");
				params.put("status", payment.getStatus());
			}
			if (StringUtils.isNotBlank(payment.getPaymentBillCode())) {
				listSb.append(" AND a.payment_bill_code = :paymentBillCode ");
				params.put("paymentBillCode", payment.getPaymentBillCode());
			}
			if (StringUtils.isNotBlank(payment.getPaymentBillName())) {
				listSb.append(" AND a.payment_bill_name LIKE :paymentBillName ");
				params.put("paymentBillName", "%"+payment.getPaymentBillName().trim()+"%");
			}
			if (StringUtils.isNotBlank(payment.getCreateTimeBegin())) {
				listSb.append(" AND DATE_FORMAT(a.create_datetime,'%Y-%m-%d') >= :createTimeBegin ");
				params.put("createTimeBegin", payment.getCreateTimeBegin());
			}
			if (StringUtils.isNotBlank(payment.getCrateTimeEnd())) {
				listSb.append(" AND DATE_FORMAT(a.create_datetime,'%Y-%m-%d') <= :crateTimeEnd ");
				params.put("crateTimeEnd", payment.getCrateTimeEnd());
			}
		}
		listSb.append(" order by a.bill_settle_datetime desc ");
		return dao.getSqlPageMapList(listSb.toString(), params, page);
	
	}

	/**
	 * @category 保存支付单
	 * @date 2015年7月27日
	 * @param payment
	 */
	public void savePayment(SetPayment payment, SysUser user) {
		Session session = factory.getCurrentSession();
		if (payment != null) {
			if (StringUtils.isBlank(payment.getPaymentBillId())) { // 新增
				payment.setPaymentBillId(UUID.randomUUID().toString());
				payment.setPaymentBillCode(CodeUtil.getNum());
				payment.setStatus(PaymentStatus.uncommit.getKey());
				payment.setCreateDatetime(new Date());
				payment.setCreateUser(user.getId());
				payment.setTotalOrders(0);
				payment.setTotalAmount(0.00);
				session.save(payment);
			} else { // 编辑
				Criteria criteria = session.createCriteria(SetPayment.class);
				criteria.add(Restrictions.eq("paymentBillId", payment.getPaymentBillId()));
				SetPayment old = (SetPayment) criteria.uniqueResult();
				if (old != null) {
					if (old.getStatus() != PaymentStatus.uncommit.getKey()) {
						throw new MSException("支付单已提交，无法编辑！");
					}
					old.setPaymentBillName(payment.getPaymentBillName());
					old.setRemark(payment.getRemark());
					old.setUpdDatetime(new Date());
					old.setUpdUser(user.getId());
					session.update(old);
				}
			}
			session.flush();
		}
	}

	/**
	 * @category 查询未加入支付单的结算单
	 * @date 2015年7月27日
	 * @param payment
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getNotAddSettlementList(Payment payment, Pagination page) {
		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append(" SELECT a.settlement_id settlementId, a.settlement_code settlementCode, a.settlement_name settlementName, "
				+ " a.settlement_type settlementType, a.org_id orgId, a.total_orders totalOrders, a.total_amount totalAmount, "
				+ " DATE_FORMAT(a.create_datetime, '%Y-%m-%d %T') createDateTime, a.create_user createUser, b.name createUserName,"
				+ " a.remarks "
				+ " FROM set_settlement a "
				+ " LEFT JOIN sys_user b on a.create_user = b.id "
				+ " WHERE a.pay_status = 1 "
				+ " AND NOT EXISTS ( SELECT c.settlement_id FROM set_payment_detail c left join set_payment d on c.payment_bill_id=d.payment_bill_id "
				+ " WHERE a.settlement_id = c.settlement_id and d.status in (0,1,2,4,6)) ");

		if (payment != null) {
			String settlementCode = payment.getSettlementCode();
			if (StringUtils.isNotBlank(settlementCode)) {
				listSb.append(" AND a.settlement_code = :settlementCode ");
			}
			String settlementName = payment.getSettlementName();
			if (StringUtils.isNotBlank(settlementName)) {
				listSb.append(" AND a.settlement_name LIKE :settlementName ");
			}
			String startDate = payment.getCreateTimeBegin();
			if (StringUtils.isNotBlank(startDate)) {
				listSb.append(" AND DATE_FORMAT(a.create_datetime, '%Y-%m-%d') >= :startDate ");
			}
			String endDate = payment.getCrateTimeEnd();
			if (StringUtils.isNotBlank(endDate)) {
				listSb.append(" AND DATE_FORMAT(a.create_datetime, '%Y-%m-%d') <= :endDate ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		if (payment != null) {
			String settlementCode = payment.getSettlementCode();
			if (StringUtils.isNotBlank(settlementCode)) {
				countQuery.setString("settlementCode", settlementCode);
				listQuery.setString("settlementCode", settlementCode);
			}
			String settlementName = payment.getSettlementName();
			if (StringUtils.isNotBlank(settlementName)) {
				countQuery.setString("settlementName", "%" + settlementName
						+ "%");
				listQuery.setString("settlementName", "%" + settlementName
						+ "%");
			}
			String startDate = payment.getCreateTimeBegin();
			if (StringUtils.isNotBlank(startDate)) {
				countQuery.setString("startDate", startDate);
				listQuery.setString("startDate", startDate);
			}
			String endDate = payment.getCrateTimeEnd();
			if (StringUtils.isNotBlank(endDate)) {
				countQuery.setString("endDate", endDate);
				listQuery.setString("endDate", endDate);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");
				if (totalAmount != null) {
					map.put("totalAmount",
							ParseNumber.toStrDecimal(totalAmount.doubleValue()));
				}
			}
		}

		return page;
	}
	/**
	 * 
	 * @category 获取一级结算单
	 * @author 邱磊
	 * @date 2015年7月22日 下午7:54:36
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getSetmenDetailList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT d.settlement_detail_id AS settlementDetailId,t.settlement_id AS settlementId,t.settlement_name AS settlementName,  ")
				.append("t.total_orders AS totalOrders,t.settlement_code AS settlementCode, c.hosp_code AS hospCode,c.hosp_name AS hospName,  ")
				.append("t.total_amount AS totalAmount,DATE_FORMAT(t.submit_datetime,'%Y-%m-%d %H:%i:%S') AS submitDatetime,a.name AS submitUser,t.remarks  ")
				.append("FROM  set_settlement_detail d   ")
				.append("LEFT JOIN set_settlement t ON d.bank_acct_trans_info_id=t.settlement_id  ")
				.append("LEFT JOIN sys_user a ON t.submit_user=a.id  ")
				.append("LEFT JOIN std_hospinfo c ON c.hosp_id=t.org_id  ")
				.append("WHERE t.settlement_type=1 AND d.settlement_id=:settlementId ");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("settlementId", map.get("settlementId"));// 结算单ID
		/*if (StringUtils.isNotBlank(map.get("hospCode"))) {// 医疗机构编码
			sql.append(" and c.hosp_code=:hospCode");
			params.put("hospCode", map.get("hospCode"));
		}*/
		if (StringUtils.isNotBlank(map.get("hospName"))) {// 医疗机构名称
			sql.append(" and c.hosp_name like :hospName");
			params.put("hospName", "%" + map.get("hospName").trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * @category 查询加入支付单的结算单
	 * @date 2015年7月27日
	 * @param payment
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getPaymentDetailList(Payment payment, Pagination page) {
		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.settlement_id settlementId, a.settlement_code settlementCode, a.settlement_name settlementName, a.settlement_type settlementType, a.org_id orgId, a.total_orders totalOrders, a.total_amount totalAmount, DATE_FORMAT( a.create_datetime, '%Y-%m-%d %T' ) createDateTime, a.create_user createUser, b.name createUserName, a.remarks FROM set_settlement a LEFT JOIN sys_user b ON a.create_user = b.id WHERE EXISTS ( SELECT c.settlement_id FROM set_payment_detail c WHERE a.settlement_id = c.settlement_id AND c.payment_bill_id = :paymentBillId ) ");

		if (payment != null) {
			String settlementCode = payment.getSettlementCode();
			if (StringUtils.isNotBlank(settlementCode)) {
				listSb.append(" AND a.settlement_code = :settlementCode ");
			}
			String settlementName = payment.getSettlementName();
			if (StringUtils.isNotBlank(settlementName)) {
				listSb.append(" AND a.settlement_name LIKE :settlementName ");
			}
			String startDate = payment.getCreateTimeBegin();
			if (StringUtils.isNotBlank(startDate)) {
				listSb.append(" AND DATE_FORMAT(a.create_datetime, '%Y-%m-%d') >= :startDate ");
			}
			String endDate = payment.getCrateTimeEnd();
			if (StringUtils.isNotBlank(endDate)) {
				listSb.append(" AND DATE_FORMAT(a.create_datetime, '%Y-%m-%d') <= :endDate ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("paymentBillId", payment.getPaymentBillId());
		listQuery.setString("paymentBillId", payment.getPaymentBillId());

		if (payment != null) {
			String settlementCode = payment.getSettlementCode();
			if (StringUtils.isNotBlank(settlementCode)) {
				countQuery.setString("settlementCode", settlementCode);
				listQuery.setString("settlementCode", settlementCode);
			}
			String settlementName = payment.getSettlementName();
			if (StringUtils.isNotBlank(settlementName)) {
				countQuery.setString("settlementName", "%" + settlementName
						+ "%");
				listQuery.setString("settlementName", "%" + settlementName
						+ "%");
			}
			String startDate = payment.getCreateTimeBegin();
			if (StringUtils.isNotBlank(startDate)) {
				countQuery.setString("startDate", startDate);
				listQuery.setString("startDate", startDate);
			}
			String endDate = payment.getCrateTimeEnd();
			if (StringUtils.isNotBlank(endDate)) {
				countQuery.setString("endDate", endDate);
				listQuery.setString("endDate", endDate);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				BigDecimal totalAmount = (BigDecimal) map.get("totalAmount");
				if (totalAmount != null) {
					map.put("totalAmount",
							ParseNumber.toStrDecimal(totalAmount.doubleValue()));
				}
			}
		}

		return page;
	}

	/**
	 * @category 添加支付明细
	 * @date 2015年7月27日
	 * @param payment
	 */
	@SuppressWarnings("unchecked")
	public void addPaymentDetail(List<Payment> paymentList, SysUser user) {
		if (paymentList != null && !paymentList.isEmpty()) {
			String paymentBillId = paymentList.get(0).getPaymentBillId();
			if (StringUtils.isBlank(paymentBillId)) {
				throw new MSException("支付单id为空，无法添加支付明细！");
			}
			Session session = factory.getCurrentSession();
			Criteria criteria = session.createCriteria(SetPayment.class);
			criteria.add(Restrictions.eq("paymentBillId", paymentBillId));
			SetPayment payment = (SetPayment) criteria.uniqueResult();
			if (payment == null) {
				throw new MSException("支付单不存在，无法添加支付明细！");
			}
			if (payment.getStatus() != PaymentStatus.uncommit.getKey()) {
				throw new MSException("支付单已提交，无法添加支付明细！");
			}
			for (Payment pay : paymentList) {
				String settlementId = pay.getSettlementId();
				if (StringUtils.isBlank(settlementId)) {
					continue;
				}
				// 查询支付单明细是否已经存在
				criteria = session.createCriteria(SetPaymentDetail.class);
				criteria.add(Restrictions.eq("paymentBillId", paymentBillId));
				criteria.add(Restrictions.eq("settlementId", settlementId));
				List<SetPaymentDetail> detailList = criteria.list();
				// 明细不存在
				if (detailList == null || detailList.isEmpty()) {
					// 添加明细1
					SetPaymentDetail detail = new SetPaymentDetail();
					detail.setPaymentBillDetailId(UUID.randomUUID().toString());
					detail.setPaymentBillId(paymentBillId);
					detail.setSettlementId(settlementId);
					detail.setCreateDatetime(new Date());
					detail.setCreateUser(user.getId());
					session.save(detail);

					// 支付明细id列表
					List<String> bankAcctTransInfoIdList = new LinkedList<String>();

					// 查询结算单
					criteria = session.createCriteria(SetSettlement.class);
					criteria.add(Restrictions.eq("settlementId", settlementId));
					SetSettlement settlement = (SetSettlement) criteria
							.uniqueResult();

					if (settlement == null) {
						continue;
					}

					if (settlement.getSettlementType() == 1) { // 一级结算单
						// 查询一级结算单明细
						criteria = session
								.createCriteria(SetSettlementDetail.class);
						criteria.add(Restrictions.eq("settlementId",
								settlementId));
						List<SetSettlementDetail> settlementDetailList = criteria
								.list();
						if (settlementDetailList != null
								&& !settlementDetailList.isEmpty()) {
							for (SetSettlementDetail settlementDetail : settlementDetailList) {
								bankAcctTransInfoIdList.add(settlementDetail
										.getBankAcctTransInfoId());
							}
						}
					} else if (settlement.getSettlementType() == 2) { // 二级结算单
						String sql = "SELECT b.bank_acct_trans_info_id bankAcctTransInfoId FROM set_settlement_detail a JOIN set_settlement_detail b ON a.bank_acct_trans_info_id = b.settlement_id WHERE a.settlement_id = :settlementId ";
						SQLQuery query = session.createSQLQuery(sql);
						query.setString("settlementId", settlementId);
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						List<Map<String, Object>> list = query.list();
						if (list != null && !list.isEmpty()) {
							for (Map<String, Object> map : list) {
								bankAcctTransInfoIdList.add((String) map
										.get("bankAcctTransInfoId"));
							}
						}
					}

					// 添加明细2
					if (!bankAcctTransInfoIdList.isEmpty()) {
						for (String bankAcctTransInfoId : bankAcctTransInfoIdList) {
							SetPaymentPayDetail payDetail = new SetPaymentPayDetail();
							payDetail.setPaymentBillDetailId(UUID.randomUUID()
									.toString());
							payDetail.setPaymentBillId(paymentBillId);
							payDetail.setSettlementId(settlementId);
							payDetail.setPayStatus(0);
							payDetail
									.setBankAcctTransInfoId(bankAcctTransInfoId);
							payDetail.setCreateDatetime(new Date());
							payDetail.setCreateUser(user.getId());
							session.save(payDetail);
						}
					}
				}
			}
			session.flush();
			updateTotalAmount(paymentBillId);
		}
	}

	/**
	 * @category 删除支付单明细
	 * @date 2015 年7月27日
	 * @param payment
	 */
	public void deletePaymentDetail(List<Payment> paymentList, SysUser user) {
		if (paymentList != null && !paymentList.isEmpty()) {
			String paymentBillId = paymentList.get(0).getPaymentBillId();
			if (StringUtils.isBlank(paymentBillId)) {
				throw new MSException("支付单id为空，无法添加支付明细！");
			}
			Session session = factory.getCurrentSession();
			for (Payment pay : paymentList) {
				String settlementId = pay.getSettlementId();
				if (StringUtils.isBlank(settlementId)) {
					continue;
				}
				// 删除支付单明细1
				String sql = "DELETE FROM set_payment_detail WHERE settlement_id = :settlementId AND payment_bill_id = :paymentBillId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("paymentBillId", paymentBillId);
				query.setString("settlementId", settlementId);
				query.executeUpdate();

				// 删除支付单明细2
				sql = "DELETE FROM set_payment_paydetail WHERE settlement_id = :settlementId AND payment_bill_id = :paymentBillId ";
				query = session.createSQLQuery(sql);
				query.setString("paymentBillId", paymentBillId);
				query.setString("settlementId", settlementId);
				query.executeUpdate();
			}
			session.flush();
			updateTotalAmount(paymentBillId);
		}
	}

	/**
	 * @category 更新支付单总条目数总金额
	 * @date 2015年7月29日
	 * @param paymentList
	 */
	private void updateTotalAmount(String paymentBillId) {
		if (StringUtils.isBlank(paymentBillId)) {
			throw new MSException("支付单id为空！");
		}
		Session session = factory.getCurrentSession();
		String sql = "UPDATE set_payment a, (SELECT  count(*) total_orders,  CASE WHEN SUM(b.total_amount) IS NULL THEN 0 ELSE SUM(b.total_amount) END total_amount FROM  set_payment_detail a JOIN set_settlement b ON a.settlement_id = b.settlement_id WHERE  a.payment_bill_id = :paymentBillId ) b SET a.total_orders = b.total_orders, a.total_amount = b.total_amount WHERE a.payment_bill_id = :paymentBillId ";
		SQLQuery query = session.createSQLQuery(sql);
		query.setString("paymentBillId", paymentBillId);
		query.executeUpdate();
		session.flush();
	}

	/**
	 * @category 提交支付单
	 * @date 2015年7月27日
	 * @param payment
	 */
	public void submitPayment(List<Payment> paymentList, SysUser user) {
		Session session = factory.getCurrentSession();
		if (paymentList != null && !paymentList.isEmpty()) {
			for (Payment payment : paymentList) {
				SetPayment old = (SetPayment) dao.getUniquerResult(SetPayment.class, payment.getPaymentBillId());
//				Criteria criteria = session.createCriteria(SetPayment.class);
//				criteria.add(Restrictions.eq("paymentBillId", payment.getPaymentBillId()));
//				SetPayment old = (SetPayment) criteria.uniqueResult();
				if (old != null) {
					if (old.getStatus() == PaymentStatus.uncommit.getKey()) {
						// 更新支付单状态
						old.setStatus(PaymentStatus.committed.getKey());
						old.setSubmitDatetime(new Date());
						old.setSubmitUser(user.getId());
						session.update(old);

						// 更新结算单状态为已纳入结算单
						String sql = "UPDATE set_settlement a SET a.pay_status = 2 , a.upd_user = :updUser, a.upd_datetime = :updDateTime WHERE EXISTS ( SELECT b.settlement_id FROM set_payment_detail b WHERE a.settlement_id = b.settlement_id AND b.payment_bill_id = :paymentBillId ) ";
						SQLQuery query = session.createSQLQuery(sql);
						query.setString("updUser", user.getId());
						query.setDate("updDateTime", new Date());
						query.setString("paymentBillId", old.getPaymentBillId());
						query.executeUpdate();
					}

				}
			}
			session.flush();
		}
	}

	/**
	 * 
	 * @category 获取根据配送企业分组确认支付列表数据（采购中心,有卫生局）
	 * @author 邱磊
	 * @date 2015年7月27日 下午7:07:25
	 * @return
	 */
	public Pagination getConfirmPayByCompHealDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT b.comp_id as delCompId,c.comp_code as compCode,c.comp_name as compName,e.area_name as areaName,")
				.append("count(a.bank_acct_trans_info_id) as totalOrders,count(f.org_id) as healCount,")
				.append("sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney from set_payment_paydetail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join std_compinfo c on c.comp_id=b.comp_id")
				.append(" left join set_payment d on d.payment_bill_id=a.payment_bill_id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join set_settlement f on f.settlement_id=a.settlement_id")
				.append(" where d.status=4 and a.payment_bill_id=:paymentBillId");

		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", map.get("paymentBillId"));
		if (null != map) {
			if (StringUtils.isNotBlank(map.get("compCode"))) {
				sql.append(" and c.comp_code=:compCode");
				params.put("compCode", map.get("compCode"));
			}
			if (StringUtils.isNotBlank(map.get("compName"))) {
				sql.append(" and c.comp_name like :compName");
				params.put("compName", map.get("compName"));
			}
		}
		sql.append(" group by c.comp_id");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取根据配送企业分组确认支付列表数据（采购中心,没有卫生局）
	 * @author 邱磊
	 * @date 2015年7月27日 下午7:07:25
	 * @return
	 */
	public Pagination getConfirmPayByCompHospDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT b.comp_id as delCompId,c.comp_code as compCode,c.comp_name as compName,e.area_name as areaName,")
				.append("count(a.bank_acct_trans_info_id) as totalOrders,count(b.hosp_id) as hospCount,")
				.append("sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney from set_payment_paydetail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join std_compinfo c on c.comp_id=b.comp_id")
				.append(" left join set_payment d on d.payment_bill_id=a.payment_bill_id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" where d.status=4 and a.payment_bill_id=:paymentBillId");

		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", map.get("paymentBillId"));
		if (null != map) {
			if (StringUtils.isNotBlank(map.get("compCode"))) {
				sql.append(" and c.comp_code=:compCode");
				params.put("compCode", map.get("compCode"));
			}
			if (StringUtils.isNotBlank(map.get("compName"))) {
				sql.append(" and c.comp_name like :compName");
				params.put("compName", map.get("compName"));
			}
		}
		sql.append(" group by c.comp_id");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 确认支付时，查看配送企业有哪些核算中心需要支付列表（采购中心查看确认支付单配送企业的详情有卫生局）
	 * @author 邱磊
	 * @date 2015年7月28日 下午1:52:12
	 * @param page
	 * @return
	 */
	public Pagination getPayInfoByHeal(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT h.hea_bur_id as heaBurId,h.hea_bur_code as heaBurCode,h.hea_bur_name as heaBurName,")
				.append("e.area_name as areaName,count(b.hosp_id) as hospCount,count(a.bank_acct_trans_info_id) as totalOrders,")
				.append("sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney ")
				.append(",i.outstand_amt as healStandAmt")
				.append(" from set_payment_paydetail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join set_payment d on d.payment_bill_id=a.payment_bill_id")
				.append(" left join set_settlement g on g.settlement_id=a.settlement_id")
				.append(" left join std_healthbureau h on h.hea_bur_id=g.org_id")
				.append(" left join set_acctinfo i on i.org_id=h.hea_bur_id")
				.append(" left join dic_area e on e.area_id=h.area_id")
				.append(" where d.status=4 and a.payment_bill_id=:paymentBillId")
				.append(" and b.comp_id=:delCompId")
				.append(" group by h.hea_bur_id");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("delCompId", page.getConditions().get("delCompId"));
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 确认支付时中心查看支付单中的配送企业下有哪些医疗机构（采购中心查看，没有卫生局）
	 * @author 邱磊
	 * @date 2015年7月28日 下午2:04:13
	 * @param page
	 * @return
	 */
	public Pagination getPayInfoByHosp(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT b.hosp_id as hospId,c.hosp_code as hospCode,c.hosp_name as hospName,e.area_name as areaName,")
				.append(" count(a.bank_acct_trans_info_id) as totalOrders,count(b.hosp_id) as hospCount,count(a.bank_acct_trans_info_id) as totalOrders,")
				.append(" sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney ")
				.append(" ,f.outstand_amt as outstandAmt")
				.append(" from set_payment_paydetail a ")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join std_hospinfo c on c.hosp_id=b.hosp_id")
				.append(" left join set_payment d on d.payment_bill_id=a.payment_bill_id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join set_acctinfo f on f.org_id=b.hosp_id")
				.append(" left join set_settlement g on g.settlement_id=a.settlement_id")
				.append(" where d.status=4 and a.payment_bill_id=:paymentBillId")
				.append(" and b.comp_id=:delCompId")
				.append(" group by b.hosp_id");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("delCompId", page.getConditions().get("delCompId"));
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * @category 查询支付明细列表
	 * @date 2015年7月27日
	 * @param payment
	 * @param page
	 * @return
	 */
	public Pagination getSettlementDetailList(Payment payment, Pagination page) {
		SetSettlement data = (SetSettlement) dao.getUniquerResult(SetSettlement.class, payment.getSettlementId());
		if (null != data){
			if (2 == data.getSettlementType()){
				StringBuffer sql = new StringBuffer()
				.append(" SELECT a.trans_id transId,a.bank_acct_trans_type bankAcctTransType,a.comp_id compId,d.comp_name compName,")
				.append(" a.hosp_id hospId,c.hosp_name hospName,e.drug_code drugCode,e.qualityspec_name drugName,")
				.append(" e.form_name formName,e.spec_name specName,e.factor,e.unit_name unit,")
				.append(" e.prod_compname prodCompName,e.material_name materialName,a.pro_price proPrice,a.recp_count recpCount ")
				.append(" FROM set_settletdetailinfo a ")
				.append(" JOIN std_hospinfo c  ON a.hosp_id = c.hosp_id ")
				.append(" JOIN  std_compinfo d  ON a.comp_id = d.comp_id ")
				.append("  inner join base_goods_item e on e.goods_code = a.goods_code ")
				.append(" WHERE EXISTS (")
				.append(" SELECT b.bank_acct_trans_info_id ")
				.append(" FROM (select * from set_settlement_detail t where t.settlement_id in")
				.append(" (select a.bank_acct_trans_info_id from set_settlement_detail a ")
				.append(" where a.settlement_id=:settlementId))b")
				.append(" WHERE a.trans_id = b.bank_acct_trans_info_id )");
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("settlementId", payment.getSettlementId());
				if (StringUtils.isNotBlank(payment.getDrugCode())) {
					sql.append("AND e.drug_code = :drugCode ");
					params.put("drugCode", payment.getDrugCode());
				}
				if (StringUtils.isNotBlank(payment.getDrugName())) {
					sql.append(" AND (e.drug_name LIKE :drugName or e.drug_name_spel like :drugNameSpel) ");
					params.put("drugName", "%"+payment.getDrugName().trim()+"%");
					params.put("drugNameSpel", "%"+payment.getDrugName().trim().toUpperCase()+"%");
				}
				if (StringUtils.isNotBlank(payment.getProdCompName())) {
					sql.append(" AND (e.comp_name LIKE :prodCompName or e.comp_spel_code like :compSpelCode)");
					params.put("prodCompName", "%"+payment.getProdCompName().trim()+"%");
					params.put("compSpelCode", "%"+payment.getProdCompName().trim().toUpperCase()+"%");
				}
				if (payment.getBankAcctTransType() != null) {
					sql.append(" AND a.bank_acct_trans_type = :bankAcctTransType ");
					params.put("bankAcctTransType", payment.getBankAcctTransType());
				}
				return dao.getSqlPageMapList(sql.toString(), params, page);
			} else {
				StringBuffer listSb = new StringBuffer();
				listSb.append(" SELECT ")
				 .append("        a.trans_id transId, ")
				 .append("        a.bank_acct_trans_type bankAcctTransType, ")
				 .append("        a.comp_id compId, ")
				 .append("        d.comp_name compName, ")
				 .append("        a.hosp_id hospId, ")
				 .append("        c.hosp_name hospName, ")
				 .append("        e.drug_code drugCode, ")
				 .append("        e.qualityspec_name drugName, ")
				 .append("        e.form_name formName, ")
				 .append("        e.spec_name specName, ")
				 .append("        e.factor, ")
				 .append("        e.unit_name unit, ")
				 .append("        e.prod_compname prodCompName, ")
				 .append("        e.material_name materialName, ")
				 .append("        a.pro_price proPrice, ")
				 .append("        a.recp_count recpCount  ")
				 .append("    FROM ")
				 .append("        set_settletdetailinfo a  ")
				 .append("   left JOIN ")
				 .append("        std_hospinfo c  ")
				 .append("            ON a.hosp_id = c.hosp_id  ")
				 .append("   left JOIN ")
				 .append("        std_compinfo d  ")
				 .append("            ON a.comp_id = d.comp_id  ")
				 .append("   left JOIN ")
				 .append("        base_goods_item e  ")
				 .append("            ON e.goods_code = a.goods_code  ")
				 .append("    WHERE 1=1 and a.trans_id ")
				 .append("    in (  select ssd1.bank_acct_trans_info_id as trans_id from ")
				 .append("       set_settlement_detail ssd1 ")
				 .append("       where  ssd1.settlement_id = :settlementId ) ");
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("settlementId", payment.getSettlementId());
				if (payment != null) {
					if (StringUtils.isNotBlank(payment.getDrugCode())) {
						listSb.append(" AND e.drug_code = :drugCode ");
						params.put("drugCode", payment.getDrugCode());
					}
					if (StringUtils.isNotBlank(payment.getDrugName())) {
						listSb.append(" AND (e.drug_name LIKE :drugName or e.drug_name_spel like :drugNameSpel) ");
						params.put("drugName", "%"+payment.getDrugName().trim()+"%");
						params.put("drugNameSpel", "%"+payment.getDrugName().trim().toUpperCase()+"%");
					}
					if (StringUtils.isNotBlank(payment.getProdCompName())) {
						listSb.append(" AND (e.comp_name LIKE :prodCompName or e.comp_spel_code like :compSpelCode)");
						params.put("prodCompName", "%"+payment.getProdCompName().trim()+"%");
						params.put("compSpelCode", "%"+payment.getProdCompName().trim().toUpperCase()+"%");
					}
					if (payment.getBankAcctTransType() != null) {
						listSb.append("AND a.bank_acct_trans_type = :bankAcctTransType ");
						params.put("bankAcctTransType", payment.getBankAcctTransType());
					}
				}
				return dao.getSqlPageMapList(listSb.toString(), params, page);
		
	}
		}
		return null;
	}
	
	
	
	/**
	 * 
	 *@category 获取支付单查看药品详情
	 *@author 邱磊
	 *@date 2015年8月10日 下午8:58:27
	 *@param payment
	 *@param page
	 *@return
	 */
	public Pagination getPaymentPayDetailList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select b.bank_acct_trans_type as bankAcctTransType,b.comp_id as compId,c.comp_name as compName,b.hosp_id as hospId,")
		.append(" d.hosp_name as hospName,b.drug_id as drugId,e.drug_code as drugCode,e.drug_name as drugName,e.form_name as formName,")
		.append(" e.spec_name as specName,e.factor,e.unit,e.prod_comp_id prodCompId,e.comp_name prodCompName,e.material_name materialName,")
		.append(" b.pro_price as proPrice,b.recp_count recpCount")
		.append(",a.pay_status as payStatus,DATE_FORMAT(a.pay_time,'%Y-%m-%d %T') as payTime,a.pay_serial_number as paySerialNumber")
		.append("  from set_payment_paydetail a")
		.append(" left join set_settletdetailinfo b on b.trans_id=bank_acct_trans_info_id")
		.append(" left join std_compinfo c on c.comp_id=b.comp_id")
		.append(" left join std_hospinfo d on d.hosp_id=b.hosp_id")
		.append(" left join view_drug e on e.drug_id=b.drug_id")
		.append(" where a.payment_bill_id=:paymentBillId and a.settlement_id=:settlementId");
		Map<String, Object> params = new HashMap<String, Object>();
		Payment form = (Payment) page.getForm();
		params.put("paymentBillId", form.getPaymentBillId());
		params.put("settlementId", form.getSettlementId());
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" AND e.drug_code = :drugCode ");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			sql.append(" AND (e.drug_name LIKE :drugName or e.drug_name_spel like :drugNameSpel) ");
			params.put("drugName", "%"+form.getDrugName().trim()+"%");
			params.put("drugNameSpel", "%"+form.getDrugName().trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(form.getProdCompName())) {
			sql.append(" AND (e.comp_name LIKE :prodCompName or e.comp_spel_code like :compSpelCode)");
			params.put("prodCompName", "%"+form.getProdCompName().trim()+"%");
			params.put("compSpelCode", "%"+form.getProdCompName().trim().toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			sql.append(" AND (c.comp_name LIKE :delCompName or c.comp_spel_code like :DelCompSpelCode)");
			params.put("delCompName", "%"+form.getDelCompName().trim()+"%");
			params.put("DelCompSpelCode", "%"+form.getDelCompName().trim().toUpperCase()+"%");
		}
		if (form.getBankAcctTransType() != null) {
			sql.append(" AND b.bank_acct_trans_type = :bankAcctTransType ");
			params.put("bankAcctTransType", form.getBankAcctTransType());
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * @category 查询支付明细列表
	 * @date 2015年7月27日
	 * @param payment
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getAddSettlementDetailList(Payment payment,
			Pagination page) {
		Session session = factory.getCurrentSession();
		Criteria criteria = session.createCriteria(SetSettlement.class);
		criteria.add(Restrictions.eq("settlementId", payment.getSettlementId()));
		SetSettlement settlement = (SetSettlement) criteria.uniqueResult();
		StringBuffer listSb = new StringBuffer();

		if (settlement != null) {
			if (settlement.getSettlementType() == 1) { // 一级结算单
				listSb.append("SELECT a.trans_id transId, a.bank_acct_trans_type bankAcctTransType, a.comp_id compId, d.comp_name compName, a.hosp_id hospId, c.hosp_name hospName, e.drug_id drugId, e.drug_code drugCode, e.drug_name drugName, e.form_name formName, e.spec_name specName, e.factor, e.unit, e.prod_comp_id prodCompId, e.comp_name prodCompName, e.material_name materialName, a.pro_price proPrice, a.recp_count recpCount FROM set_settletdetailinfo a JOIN std_hospinfo c ON a.hosp_id = c.hosp_id JOIN std_compinfo d ON a.comp_id = d.comp_id JOIN view_drug e ON a.drug_id = e.drug_id WHERE 1 = 1 AND EXISTS ( SELECT b.bank_acct_trans_info_id FROM set_settlement_detail b WHERE a.trans_id = b.bank_acct_trans_info_id AND b.settlement_id = :settlementId ) ");
			} else if (settlement.getSettlementType() == 2) { // 二级结算单
				listSb.append("SELECT a.trans_id transId, a.bank_acct_trans_type bankAcctTransType, a.comp_id compId, d.comp_name compName, a.hosp_id hospId, c.hosp_name hospName, e.drug_id drugId, e.drug_code drugCode, e.drug_name drugName, e.form_name formName, e.spec_name specName, e.factor, e.unit, e.prod_comp_id prodCompId, e.comp_name prodCompName, e.material_name materialName, a.pro_price proPrice, a.recp_count recpCount FROM set_settletdetailinfo a JOIN std_hospinfo c ON a.hosp_id = c.hosp_id JOIN std_compinfo d ON a.comp_id = d.comp_id JOIN view_drug e ON a.drug_id = e.drug_id WHERE 1 = 1 AND EXISTS ( SELECT b.bank_acct_trans_info_id  FROM (select * from set_settlement_detail t where t.settlement_id in (select a.bank_acct_trans_info_id from set_settlement_detail a  where a.settlement_id=:settlementId))b WHERE a.trans_id = b.bank_acct_trans_info_id  ) ");
			}
		}

		if (payment != null) {
			String drugCode = payment.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				listSb.append(" AND e.drug_code = :drugCode ");
			}
			String drugName = payment.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				listSb.append(" AND e.drug_name LIKE :drugName ");
			}
			String prodCompName = payment.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				listSb.append(" AND e.comp_name LIKE :prodCompName ");
			}
			Integer bankAcctTransType = payment.getBankAcctTransType();
			if (bankAcctTransType != null) {
				listSb.append(" AND a.bank_acct_trans_type = :bankAcctTransType ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("settlementId", payment.getSettlementId());
		listQuery.setString("settlementId", payment.getSettlementId());

		if (payment != null) {
			String drugCode = payment.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				countQuery.setString("drugCode", drugCode);
				listQuery.setString("drugCode", drugCode);
			}
			String drugName = payment.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				countQuery.setString("drugName", "%" + drugName + "%");
				listQuery.setString("drugName", "%" + drugName + "%");
			}
			String prodCompName = payment.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				countQuery.setString("prodCompName", "%" + prodCompName + "%");
				listQuery.setString("prodCompName", "%" + prodCompName + "%");
			}
			Integer bankAcctTransType = payment.getBankAcctTransType();
			if (bankAcctTransType != null) {
				countQuery.setInteger("bankAcctTransType", bankAcctTransType);
				listQuery.setInteger("bankAcctTransType", bankAcctTransType);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				Integer bankAcctTransType = Integer.valueOf((Byte) map
						.get("bankAcctTransType"));
				if (bankAcctTransType != null) {
					if (bankAcctTransType == 1) {
						map.put("bankAcctTransTypeName", "采购明细");
					} else if (bankAcctTransType == 2) {
						map.put("bankAcctTransTypeName", "退货明细");
					}
				}
				BigDecimal proPrice = (BigDecimal) map.get("proPrice");
				if (proPrice != null) {
					map.put("proPrice",
							ParseNumber.toStrDecimal(proPrice.doubleValue()));
				}
			}
		}

		return page;
	}

	/**
	 * @category 根据id查询支付单
	 * @date 2015年7月27日
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public SetPayment getPaymentById(String id) {
//		Session session = factory.getCurrentSession();
//		Criteria criteria = session.createCriteria(SetPayment.class);
//		criteria.add(Restrictions.eq("paymentBillId", id));
//		return (SetPayment) criteria.uniqueResult();
		StringBuffer sql = new StringBuffer()
		.append("select a.status, a.payment_bill_id as paymentBillId,a.payment_bill_code as paymentBillCode,a.payment_bill_name as paymentBillName,")
		.append("a.total_orders as totalOrders,a.total_amount as totalAmount,DATE_FORMAT(a.create_datetime,'%Y-%m-%d %T') as createDatetime,")
		.append("c.name as createUser,a.remark,b.name as auditUser,DATE_FORMAT(a.audit_datetime,'%Y-%m-%d %T') as auditDatetime,a.audit_remark as auditRemark from set_payment a")
		.append(" left join sys_user b on a.audit_user=b.id")
		.append(" left join sys_user c on c.id=a.create_user")
		.append(" where a.payment_bill_id=:paymentBillId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", id);
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
		if (null != list && list.size() > 0){
			Map<String, Object> map = list.get(0);
			SetPayment data = new SetPayment();
			data.setStatus(map.get("status")==null?0:ParseNumber.toInteger(map.get("status").toString()));
			data.setPaymentBillId(map.get("paymentBillId")==null?"":map.get("paymentBillId").toString());
			data.setPaymentBillCode(map.get("paymentBillCode")==null?"":map.get("paymentBillCode").toString());
			data.setPaymentBillName(map.get("paymentBillName")==null?"":map.get("paymentBillName").toString());
			data.setTotalOrders(map.get("totalOrders")==null?0:ParseNumber.toInteger(map.get("totalOrders").toString()));
			data.setTotalAmountStr(map.get("totalAmount")==null?"":ParseNumber.toStrDecimal(ParseNumber.toDouble(map.get("totalAmount").toString())));
			data.setCreateDatetimeStr(map.get("createDatetime")==null?"":map.get("createDatetime").toString());
			data.setCreateUser(map.get("createUser")==null?"":map.get("createUser").toString());
			data.setRemark(map.get("remark")==null?"":map.get("remark").toString());
			data.setAuditUser(map.get("auditUser")==null?"":map.get("auditUser").toString());
			data.setAuditDatetimeStr(map.get("auditDatetime")==null?"":map.get("auditDatetime").toString());
			data.setAuditRemark(map.get("auditRemark")==null?"":map.get("auditRemark").toString());
			return data;
		}
		return null;
	}

	/**
	 * @category 删除支付单明细
	 * @date 2015年7月27日
	 * @param payment
	 */
	@SuppressWarnings("unchecked")
	public void deletePayment(List<Payment> paymentList, SysUser user) {
		if (paymentList != null && !paymentList.isEmpty()) {
			List<String> ids = new LinkedList<String>();
			for (Payment payment : paymentList) {
				ids.add(payment.getPaymentBillId());
			}
			// 查询待删除的支付单列表
			Session session = factory.getCurrentSession();
			Criteria criteria = session.createCriteria(SetPayment.class);
			criteria.add(Restrictions.in("paymentBillId", ids));
			List<SetPayment> oldList = criteria.list();
			if (oldList != null && !oldList.isEmpty()) {
				for (SetPayment payment : oldList) {
					// 删除状态为未提交的支付单
					if (payment.getStatus() == PaymentStatus.uncommit.getKey()) {

						// 删除支付明细1
						String sql = "DELETE FROM set_payment_detail WHERE payment_bill_id = :paymentBillId ";
						SQLQuery query = session.createSQLQuery(sql);
						query.setString("paymentBillId",
								payment.getPaymentBillId());
						query.executeUpdate();

						// 删除支付明细2
						sql = "DELETE FROM set_payment_paydetail WHERE payment_bill_id = :paymentBillId  ";
						query = session.createSQLQuery(sql);
						query.setString("paymentBillId",
								payment.getPaymentBillId());
						query.executeUpdate();

						// 删除支付单
						session.delete(payment);
					}
				}
				session.flush();
			}
		}
	}

	

	/**
	 * @category 更新支付单状态
	 * @author 韩守松
	 * @date 2015年7月29日
	 * @param @param setPayment
	 * @param @param user
	 * @param @return
	 */
	public Integer updatePaymentStatusById(SetPayment setPayment, SysUser user) {
		Session session = factory.getCurrentSession();
		String paymentBillId = setPayment.getPaymentBillId();
		String updUser = user.getName();
		setPayment.setUpdUser(updUser);
		StringBuffer hql = new StringBuffer();
		hql.append("update set_payment set ");
		hql.append("status = :status ");
		hql.append(", audit_remark = :auditRemark ");
		hql.append(", audit_datetime= SYSDATE()");
		hql.append(", audit_user=:auditUser");
		hql.append(", upd_user = :updUser ");
		hql.append(", upd_datetime = SYSDATE() ");
		hql.append("where 1 = 1 ");
		hql.append("and payment_bill_id = :paymentBillId ");
		SQLQuery query = session.createSQLQuery(hql.toString());
		query.setInteger("status", setPayment.getStatus());
		query.setString("auditRemark", setPayment.getAuditRemark());
		query.setString("updUser", user.getId());
		query.setString("auditUser", user.getId());
		query.setString("paymentBillId", paymentBillId);
		Integer result = query.executeUpdate();
		session.flush();
		return result;
	}

	/**
	 * 
	 * @category 获取配送企业支付单待支付药品数量
	 * @author 邱磊
	 * @date 2015年7月28日 下午3:03:23
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getDelPayCount(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT count(a.bank_acct_trans_info_id) as totalOrders")
				.append(" from set_payment_paydetail a")
				.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id")
				.append(" left join std_compinfo c on c.comp_id=b.comp_id")
				.append(" left join set_payment d on d.payment_bill_id=a.payment_bill_id")
				.append(" left join dic_area e on e.area_id=c.area_id")
				.append(" left join set_settlement f on f.settlement_id=a.settlement_id")
				.append(" where a.payment_bill_id=:paymentBillId and c.comp_id=:delCompId and a.pay_status=0");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("delCompId", page.getConditions().get("delCompId"));
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(),
				params);
	}

	/**
	 * 
	 * @category author 邱磊
	 * @date 2015年7月28日 下午8:32:03
	 * @param paymentBillId
	 * @param delCompId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getHealPayBalance(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select * from (SELECT d.outstand_amt-sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney")
				.append(" from set_payment_paydetail a ")
				.append(" left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id")
				.append(" left join set_settlement c on a.settlement_id=c.settlement_id")
				.append(" left join set_acctinfo d on d.org_id=c.org_id")
				.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId")
				.append(" group by c.org_id )p where p.summoney<0");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("delCompId", page.getConditions().get("delCompId"));
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(),
				params);
	}

	/**
	 * 
	 * @category 获取医疗机构心支付后余额小于0的
	 * @author 邱磊
	 * @date 2015年7月28日 下午8:32:03
	 * @param paymentBillId
	 * @param delCompId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getHospPayBalance(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select * from (SELECT d.outstand_amt-sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney")
				.append("  from set_payment_paydetail a ")
				.append(" left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id")
				.append(" left join set_acctinfo d on d.org_id=b.hosp_id")
				.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId")
				.append(" group by b.hosp_id) p where p.summoney<0");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("delCompId", page.getConditions().get("delCompId"));
		return (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(),
				params);
	}

	/**
	 * 
	 * @category 支付确认按钮操作
	 * @author 邱磊
	 * @date 2015年7月28日 下午9:35:48
	 * @param page
	 */
	public void confirmPay(Pagination page) {
		reduceHealAmt(page.getConditions().get("paymentBillId"), page
				.getConditions().get("delCompId"),page.getConditions().get("payUser"));
		reduceHospAmt(page.getConditions().get("paymentBillId"), page
				.getConditions().get("delCompId"),page.getConditions().get("payUser"));
		updatePayStatus(page);
		updateAllPayStatus(page.getConditions().get("paymentBillId"), page.getConditions().get("payUser"));
	}

	/**
	 * 
	 * @category 扣减核算中心账户余额
	 * @author 邱磊
	 * @date 2015年7月28日 下午9:48:38
	 * @param paymentBillId
	 * @param delCompId
	 */
	private void reduceHealAmt(String paymentBillId, String delCompId,String updUser) {
		StringBuffer sql = new StringBuffer()
		.append("insert into set_accttransinfo SELECT UUID(),c.org_id,2,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney,")
		.append("SYSDATE(),(d.outstand_amt-sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end)),null,SYSDATE(),:updUser")
		.append(" from set_payment_paydetail a ")
		.append("  left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id")
		.append(" left join set_settlement c on a.settlement_id=c.settlement_id")
		.append(" left join set_acctinfo d on d.org_id=c.org_id")
		.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId")
		.append(" group by c.org_id ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", paymentBillId);
		params.put("delCompId", delCompId);
		params.put("updUser", updUser);
		dao.executeSql(sql.toString(), params);
		sql = new StringBuffer()
		.append("update set_acctinfo o,(SELECT c.org_id,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney")
		.append(" from set_payment_paydetail a ")
		.append(" left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id")
		.append(" left join set_settlement c on a.settlement_id=c.settlement_id")
		.append(" left join set_acctinfo d on d.org_id=c.org_id")
		.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId")
		.append(" group by c.org_id) p ")
		.append(" set o.outstand_amt=o.outstand_amt-p.summoney,")
		.append(" o.outstand_amt_upd_dttm=sysdate(),")
		.append(" o.upd_datetime=sysdate(),")
		.append(" o.upd_user=:updUser")
		.append(" where o.org_id=p.org_id");
		dao.executeSql(sql.toString(), params);
	}

	/**
	 * 
	 * @category 扣减医疗机构余额
	 * @author 邱磊
	 * @date 2015年7月28日 下午9:48:58
	 * @param paymentBillId
	 * @param delCompId
	 */
	private void reduceHospAmt(String paymentBillId, String delCompId,String updUser) {
		StringBuffer sql = new StringBuffer()
		.append("insert into set_accttransinfo SELECT UUID(),b.hosp_id,2,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney,")
		.append("SYSDATE(),(d.outstand_amt-sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end)),null,SYSDATE(),:updUser")
		.append(" from set_payment_paydetail a ")
		.append(" left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id")
		.append(" left join set_acctinfo d on d.org_id=b.hosp_id")
		.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId")
		.append(" group by b.hosp_id ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", paymentBillId);
		params.put("delCompId", delCompId);
		params.put("updUser", updUser);
		dao.executeSql(sql.toString(), params);
		sql = new StringBuffer()
		.append("update set_acctinfo o,(SELECT b.hosp_id,sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney")
		.append(" from set_payment_paydetail a ")
		.append(" left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id")
		.append(" left join set_acctinfo d on d.org_id=b.hosp_id")
		.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId")
		.append(" group by b.hosp_id) p ")
		.append(" set o.outstand_amt=o.outstand_amt-p.summoney,")
		.append(" o.outstand_amt_upd_dttm=sysdate(),")
		.append(" o.upd_datetime=sysdate(),")
		.append(" o.upd_user=:updUser")
		.append(" where o.org_id=p.hosp_id");
		dao.executeSql(sql.toString(), params);
	}

	/**
	 * 
	 * @category 更新支付明细支付状态，支付时间，支付人，支付流水
	 * @author 邱磊
	 * @date 2015年7月28日 下午9:55:34
	 * @param page
	 */
	private void updatePayStatus(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("update set_payment_paydetail a left join set_settletdetailinfo b on b.trans_id=a.bank_acct_trans_info_id ")
				.append(" set a.pay_status=1,a.pay_serial_number=CONCAT(DATE_FORMAT(SYSDATE(),'%Y%m%d%H%i%S'),LPAD(ROUND(ROUND(RAND(),5)*100000),5,'0')),")
				.append(" a.pay_time=SYSDATE(),a.pay_user=:payUser")
				.append(" where a.payment_bill_id=:paymentBillId and b.comp_id=:delCompId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("payUser", page.getConditions().get("payUser"));
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("delCompId", page.getConditions().get("delCompId"));
		dao.executeSql(sql.toString(), params);
	}

	/**
	 * 
	 * @category 更新支付单状态（大支付单）
	 * @author 邱磊
	 * @date 2015年7月29日 上午10:20:46
	 * @param page
	 */
	private void updateAllPayStatus(String paymentBillId, String billSettleUser) {
		StringBuffer sql = new StringBuffer()
				.append("update set_payment o set o.status=(select case count(a.bank_acct_trans_info_id) when 0 then 6 else 4 end ")
				.append(" from set_payment_paydetail a")
				.append(" where a.payment_bill_id=:paymentBillId and a.pay_status=0), ")
				.append(" o.bill_settle_datetime=SYSDATE(),o.bill_settle_user=:billSettleUser")
				.append(" where o.payment_bill_id=:paymentBillId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("paymentBillId", paymentBillId);
		params.put("billSettleUser", billSettleUser);
		dao.executeSql(sql.toString(), params);
	}
	
	
	/**
	 * @category 获取按配送企业分组的支付单数据【含核算中心】
	 * @author 应晓川
	 * @date 2015年7月29日09:20:33
	 * @param 
	 */
	@Override
	public Pagination getCompBySetCenterList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("select sp.payment_bill_code paymentBillCode,sp.payment_bill_name paymentBillName,ssi.comp_id as compId,")
		.append("sc.comp_name AS compName,")
		.append("sc.comp_code AS compCode,")
		.append("da.ar_fullname as areaName,")
		.append("count(DISTINCT ss.org_id) AS totalOrders,")
		.append(" sp.payment_bill_id AS paymentBillId ,")
		.append("sum(case ssi.bank_acct_trans_type when 1 then (ssi.pro_price*ssi.recp_count) when 2 then (-ssi.pro_price*ssi.recp_count) end) as totalAmount")
		.append(" from set_payment sp")
		.append(" left join set_payment_paydetail spp on sp.payment_bill_id = spp.payment_bill_id ")
		.append(" left join set_settletdetailinfo ssi on spp.bank_acct_trans_info_id = ssi.trans_id ")
		.append(" left join std_compinfo sc on ssi.comp_id =  sc.comp_id")
		.append(" left join dic_area da on da.area_id  = sc.area_id")
		.append(" left join set_settlement ss on ss.settlement_id = spp.settlement_id")
		.append(" where ")
		.append(" sp.status = 4");
		String compName = page.getConditions().get("compName");
		if (StringUtils.isNotBlank(compName)) {
			sql.append(" and sc.comp_name like :compName ");
			page.getConditions().put("compName", "%"+compName+"%");
		}
		sql.append(" group by ssi.comp_id, sp.payment_bill_code   order by sp.payment_bill_code");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	/**
	 * @category 获取按配核算中心分组的支付单数据【含核算中心】
	 * @author 应晓川
	 * @date   2015年7月28日20:01:15
	 * @param  
	 */
	@Override
	public Pagination getPaymentBySetCenterList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		
	/*	sql.append(" select sh.hea_bur_id as  setCenterId ,")
		.append(" sh.hea_bur_code as  setCenterCode,")
		.append(" sh.hea_bur_name as  setCenterName,")
		.append("   da.ar_fullname as areaName,")
		.append("   count(ssi.hosp_id) as totalHosp,")
		.append("   sp.total_amount as totalAmount,")
		.append("   sa.available_amt as outstandAmt")
		.append(" from set_settletdetailinfo ssi,")
		.append("  set_payment_paydetail spp,")
		.append("  set_payment sp,")
		.append("  std_healthbureau sh,")
		.append("  set_settlement ss,")
		.append("  set_acctinfo sa,")
		.append("  dic_area da")
		.append(" where sa.org_id = sh.hea_bur_id ")
		.append("  and spp.bank_acct_trans_info_id = ssi.trans_id")
		.append("  and spp.payment_bill_id = sp.payment_bill_id ")
		.append("  and spp.settlement_id = ss.settlement_id")
		.append("  and ss.org_id = sh.hea_bur_id")
		.append("  and da.area_id = sh.area_id");
		sql.append("  and ssi.comp_id =:compId ")
		.append(" and sp.payment_bill_id=:paymentBillId")
		.append(" group by ss.org_id");*/
		
		sql.append(" select a.payment_bill_id as paymentBillId,c.comp_id as compId, b.org_id as setCenterId,d.hea_bur_code as setCenterCode,d.hea_bur_name as setCenterName,e.area_name as areaName, ");
		sql.append(" count(DISTINCT c.hosp_id) as totalHosp, ");
		sql.append(" sum(case c.bank_acct_trans_type when 1 then (c.pro_price*c.recp_count) when 2 then (-c.pro_price*c.recp_count) end) as totalAmount, ");
		sql.append(" f.outstand_amt as outstandAmt ");
		sql.append(" from set_payment_paydetail a  ");
		sql.append(" left join set_settlement b on b.settlement_id=a.settlement_id ");
		sql.append(" left join set_settletdetailinfo c on c.trans_id=a.bank_acct_trans_info_id ");
		sql.append(" left join std_healthbureau d on d.hea_bur_id=b.org_id ");
		sql.append(" left join dic_area e on e.area_id=d.area_id ");
		sql.append(" left join set_acctinfo f on f.org_id=b.org_id ");
		sql.append(" where a.payment_bill_id = :paymentBillId");
		sql.append(" and c.comp_id = :compId ");
		sql.append(" group by b.org_id ");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}
	
	/**
	 * @category 获取按医疗机构分组的支付单数据【含核算中心】
	 * @author 应晓川
	 * @date 2015年7月29日09:20:33
	 * @param 
	 */
	@Override
	public Pagination getPaymentByHospList(Pagination page) {
		StringBuffer sql = new StringBuffer();
	/*	sql.append(" select sh.hosp_id as hospId, ")
		 .append(" sh.hosp_code as hospCode, ")
		 .append(" sh.hosp_name as hospName, ")
		 .append(" da.ar_fullname as areaName, ")
		 .append(" sa.pending_amt as pendingAmt, ")
		 .append(" sa.outstand_amt as outstandAmt, ")
		 .append(" count(ssdi.drug_id) as totalDrugs ")
		 .append(" from ")
		 .append(" set_payment sp inner join  set_payment_detail spd on sp.payment_bill_id = spd.payment_bill_id, ")
		 .append(" set_settlement ss inner join set_settlement_detail ssd on ss.settlement_id = ssd.settlement_id, ")
		 .append(" set_settlement ss2 inner join std_hospinfo sh on ss2.org_id =sh.hosp_id ")
		 .append(" left join  dic_area da on da.area_id = sh.area_id ")
		 .append(" inner join set_acctinfo sa on ss2.org_id = sa.org_id ")
		 .append(" inner join set_settletdetailinfo ssdi on ssdi.hosp_id = ss2.org_id ")
		 .append(" where  ")
		 .append("  sp.status =4 ")
		 .append(" and ss.org_id = :setCenterId ")
		 .append(" and ssd.bank_acct_trans_info_id = ss2.settlement_id ")
		 .append(" group by sh.hosp_id");*/
		
		sql.append(" select c.hosp_id as hospId, b.org_id as setCenterId, a.payment_bill_id as paymentBillId,c.comp_id as compId, d.hosp_code as hospCode,d.hosp_name as hospName,e.area_name as areaName, ");
		sql.append(" count(c.trans_id) as totalDrugs, ");
		sql.append(" sum(case c.bank_acct_trans_type when 1 then (c.pro_price*c.recp_count) when 2 then (-c.pro_price*c.recp_count) end) as pendingAmt, ");
		sql.append(" f.outstand_amt as outstandAmt  ");
		sql.append(" from set_payment_paydetail a  ");
		sql.append(" left join set_settlement b on b.settlement_id=a.settlement_id ");
		sql.append(" left join set_settletdetailinfo c on c.trans_id=a.bank_acct_trans_info_id ");
		sql.append(" left join std_hospinfo d on d.hosp_id=c.hosp_id ");
		sql.append(" left join dic_area e on e.area_id=d.area_id ");
		sql.append(" left join set_acctinfo f on f.org_id=c.hosp_id ");
		sql.append(" where a.payment_bill_id = :paymentBillId ");
		sql.append(" and c.comp_id = :compId ");
		sql.append(" and b.org_id = :setCenterId ");
		sql.append(" group by c.hosp_id ");

		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	/**
	 * @category 获取支付单分组的药品数据
	 * @author 应晓川
	 * @date   2015年7月29日15:21:51
	 * @param  
	 */
	@Override
	public Pagination getSettletdetailinfoByHospList(Pagination page) {
		StringBuffer sql = new StringBuffer();
/*		sql.append(" select vd.drug_code as drugCode, ")
		.append(" vd.drug_name as drugName, ")
		.append(" vd.form_name as formName, ")
		.append(" vd.spec_name as specName, ")
		.append(" vd.factor as factor, ")
		.append(" vd.unit as unit, ")
		.append(" vd.material_name as materialName, ")
		.append(" vd.comp_name as prodName, ")
		.append(" ssdi.pro_count as proCount, ")
		.append(" ssdi.pro_price as proPrice, ")
		.append(" ssdi.bank_acct_trans_type as bankAcctTransType ")
		.append(" from ")
		.append(" set_settletdetailinfo ssdi inner join  set_payment_paydetail spp  on ssdi.trans_id = spp.bank_acct_trans_info_id ") 
		.append(" inner join set_payment sp on sp.payment_bill_id = spp.payment_bill_id  ")
		.append(" left join view_drug vd on vd.drug_id = ssdi.drug_id  ")
		.append(" where ssdi.hosp_id = :hospId ");
		*/
		sql.append(" select d.drug_id as drugId,d.drug_code as drugCode,d.drug_name as drugName,d.form_name as formName,d.spec_name as specName, ");
		sql.append(" d.factor,d.unit,d.material_name as materialName,d.comp_name as prodName,c.bank_acct_trans_type as bankAcctTransType, ");
		sql.append(" c.recp_count as proCount,c.pro_price as proPrice  ");
		sql.append(" from set_payment_paydetail a  ");
		sql.append(" left join set_settlement b on b.settlement_id=a.settlement_id ");
		sql.append(" left join set_settletdetailinfo c on c.trans_id=a.bank_acct_trans_info_id ");
		sql.append(" left join view_drug d on d.drug_id=c.drug_id ");
		sql.append(" where a.payment_bill_id = :paymentBillId ");
		sql.append(" and c.comp_id = :compId ");
		sql.append(" and b.org_id = :setCenterId ");
		sql.append(" and c.hosp_id = :hospId ");

		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

/*				                           支付单支付流程【不含核算中心】			    				*/	
	
	/**
	 * @category 获取按配送企业分组的支付单数据【不含核算中心】
	 * @author 应晓川
	 * @date 2015年7月29日09:20:33
	 * @param 
	 */
	@Override
	public Pagination getPaymentByNoCompList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ssi.comp_id as compId,")
		.append("sc.comp_name AS compName,")
		.append("sc.comp_code AS compCode,")
		.append("da.ar_fullname as areaName,")
		.append("count(ss.org_id) AS totalOrders,")
		.append(" sp.payment_bill_id AS paymentBillId ,")
		.append("sp.total_amount as totalAmount")
		.append(" from set_payment sp,set_payment_paydetail spp, set_settletdetailinfo ssi,std_compinfo sc,")
		.append(" dic_area da,set_settlement ss ")
		.append(" where sp.payment_bill_id = spp.payment_bill_id ")
		.append(" and spp.bank_acct_trans_info_id = ssi.trans_id")
		.append(" and ssi.comp_id =  sc.comp_id")
		.append(" and da.area_id  = sc.area_id")
		.append(" and ss.settlement_id = spp.settlement_id")
		.append(" and ss.settlement_type = 1 ")
		.append(" and sp.status = 4");
		String compName = page.getConditions().get("compName");
		if (StringUtils.isNotBlank(compName)) {
			sql.append(" and sc.comp_name like :compName ");
			page.getConditions().put("compName", "%"+compName+"%");
		}
		sql.append(" group by ssi.comp_id");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	/**
	 * @category 获取医疗机构分组的支付单明细数据【不含核算中心】
	 * @author 应晓川
	 * @date   2015年7月29日17:41:55
	 * @param  
	 */
	@Override
	public Pagination getHospByNoCompList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select sh.hosp_id as hospId, ")
		.append("  sh.hosp_code as hospCode,   ")
		.append("  sh.hosp_name as hospName,   ")
		.append("  da.ar_fullname as areaName,   ")
		.append("  sp.total_amount as totalAmount , ")
		.append("  sa.outstand_amt as outstandAmt,   ")
		.append("  count(ssdi.drug_id) as totalDrugs   ")
		.append(" from ")
		.append("  set_payment sp inner join  set_payment_paydetail spp on sp.payment_bill_id = spp.payment_bill_id ")
		.append("  inner join set_settletdetailinfo ssdi on ssdi.trans_id = spp.bank_acct_trans_info_id ")
		.append("  left join std_hospinfo sh on ssdi.hosp_id =sh.hosp_id ")
		.append("  left join  dic_area da on da.area_id = sh.area_id  ")
		.append("  inner join set_acctinfo sa on ssdi.hosp_id = sa.org_id ")
		.append(" WHERE ssdi.comp_id = :compId ")
		.append(" order by sh.hosp_id ");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	
	/**
	 * @category 记录中心账户金额变更信息
	 * @author 韩守松
	 * @date   2015年8月11日
	 * @param  @return
	 */
	public void getCenterUser(SetAcctTransInfo setAcctTransInfo){
		Session session = factory.getCurrentSession();
		setAcctTransInfo.setTransId(UUID.randomUUID().toString());
		session.saveOrUpdate(setAcctTransInfo);
		session.flush();
	}
	

	/**
	 * @category 根据支付单id，配送企业id获取信息
	 * @author 韩守松
	 * @date   2015年8月11日
	 * @param  @param setAcctTransInfo
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getAcctTrans(String BillID,String CompId){
		StringBuffer hql =  new StringBuffer();
		hql.append(" select ");
		hql.append("  sa.org_id,sa.org_code,sa.org_name,sa.org_type, ");
		hql.append(" (case ssd.bank_acct_trans_type when 1 then 1 when 2 then 2 end) as status_type, ");
		hql.append(" sa.outstand_amt,sa.outstand_amt_upd_dttm,sa.available_amt,sa.available_amt_upd_dttm, ");
		hql.append(" sa.pending_amt,sa.pending_amt_upd_dttm,sa.upd_datetime,sa.upd_user, ");
		hql.append(" sum(case ssd.bank_acct_trans_type when 1 then (ssd.pro_price*ssd.recp_count) when 2 then (-ssd.pro_price*ssd.recp_count) end) as summoney ");
		hql.append(" from  set_acctinfo sa  ");
		hql.append(" left join set_settlement ssm on sa.org_id = ssm.org_id ");
		hql.append(" left join set_payment_paydetail spp on ssm.settlement_id = spp.settlement_id ");
		hql.append(" left join set_settletdetailinfo ssd on spp.bank_acct_trans_info_id = ssd.trans_id ");
		hql.append(" where ");
		hql.append(" spp.payment_bill_id = :BillID ");
		hql.append(" and ssd.comp_id = :CompId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("BillID", BillID);
		params.put("CompId", CompId);
		return (List<Map<String, Object>>) dao.getSqlMapList(hql.toString(),params);
	}

	
}
